Release 10.1A: OpenEdge Development:
Progress Dynamics Advanced Development

Modifying the WHERE clause at run time

You can modify the base database query WHERE clause in many ways at run time. When you link multiple SmartDataObjects together in a parent-child relationship, this modification is done automatically.

Using ForeignFields to filter a dependent query

If you want to browse through Customer records and then through Orders of a selected Customer, you can do this by creating separate SDOs, one for Customer records and one for Orders, and then linking them together. When you do this, the Order SDO uses a property called ForeignFields to modify the WHERE clause dynamically, so that the Order SDO that was originally defined with the simple query FOR EACH Order is now refined to select EACH Order WHERE Order.CustNum = <CustNum>. The value of the <CustNum> field is retrieved from the parent Customer SDO each time a new Customer is selected and plugged into the Order query before that query is reopened. You can define the appropriate ForeignFields value either in the AppBuilder for static window procedures or in the Container Builder for dynamic windows.

For the Customer/Order example, the initialization code for the Order SDO assigns the value Order.CustNum,CustNum to the ForeignFields property. Each time a new Customer is selected at run time, the dataAvailable event is published, and that event procedure in the Order SDO queries the Customer SDO for the current value of the foreign field CustNum. Note that this field refers to a field in the Customer RowObject table, and therefore is not qualified by a table name (it could, in fact, be renamed from the actual database field it is derived from). This field’s value is used to set the property ForeignValues, which holds the current values of a SmartDataObject’s ForeignFields. The WHERE clause of the Order SDO is then modified for you to insert the phrase Order.CustNum = <CustNum>, where <CustNum> is filled in from the ForeignValues property, and the query is prepared and opened. A dependent query can contain multiple foreign fields, and the values for them are kept in sync with the list of fields that make up the foreign key.

The functions setQueryWhere and setQuerySort

The earliest versions of the Version 9 SmartObjects supported two basic functions to manipulate the WHERE clause: setQueryWhere and setQuerySort. These work well in straightforward cases, but setQueryWhere, in particular, does not deal well with successive changes to the WHERE clause or other more complex needs. Therefore, we do not recommend that you use setQueryWhere in new application code. The setQuerySort function can be used to change the sort sequence of a query, but remember that any query that has a WHERE clause or other filter applied to it is sorted automatically based on the indexes used to satisfy the query, and this is typically the most appropriate sort sequence.

If you want to allow users to sort a result set in different ways after it has been retrieved, you can do this more efficiently by manipulating the temp-table query instead of sorting and reopening the database query, as we discuss later in this section.

Instead, you should generally use the addQueryWhere and assignQuerySelection functions described below. These are much more flexible and can operate more efficiently.

The resortQuery function

The resortQuery() function takes an existing query and resorts it according to criteria you specify. For example:

DYNAMIC-FUNCTION('resortQuery':U IN h_dorder, INPUT 'BY ordernum DESCEND').

The setBaseQuery function

There is an additional function that can be of use in some cases, if you want to set a basic filter on a dataset so that it cannot be lost by changes to the WHERE clause later on. The property that stores the base query is BaseQuery, whose initial value is the query defined when the SDO is created. To apply a filter at run time that is not removed by other changes, you can reset this property, effectively overriding the basic definition of the SDO’s query in that instance. In the following example, we have run setOpenQuery to change the basic query to return only Customer records where the State field equals MA. All calls to the other WHERE clause functions then append their WHERE clause to this new basic query definition. Note that because we’re resetting the entire Open Query statement, we need to specify the query starting with FOR EACH.

Note: SDOs do not support the use of this function to change the database tables the query operates on.

You can override another default behavior of the SDO, which is to open its database query as soon as the SDO is initialized. You might not want to wait for that to happen if the SDO is always filtered before the data is actually used. In this case, you can reset the OpenOnInit property of the SDO mentioned earlier in this chapter to FALSE to cause it to wait until the openQuery function is run after initialization.